package com.weavernorth.updateKaoQin;

import com.weavernorth.sysap.util.SyncUtil;
import com.weavernorth.util.Logger4jUtil;
import com.weavernorth.util.dateUtils;
import org.apache.log4j.Logger;
import weaver.conn.ConnectionPool;
import weaver.conn.RecordSet;
import weaver.interfaces.schedule.BaseCronJob;
import weaver.wechat.util.Utils;

import java.sql.Connection;
import java.sql.Statement;

/**
 * @Author: bruseewang
 * @Package: com.weavernorth.updateKaoQin
 * @date: 2021/9/15 18:02
 * @description: 定时任务：插入异常考勤到建模表
 */
public class zInsertKqTimmer extends BaseCronJob {
    public Logger log = new Logger4jUtil().getLogger("/Timmer/zInsertKqTimmer/");
    private static ConnectionPool pool = ConnectionPool.getInstance();
    private Connection conn;
    private Statement statement;
    @Override
    public void execute() {
        log.info("定时任务：插入异常考勤到建模表======>");
        try {
            conn = pool.getConnection();
            statement = conn.createStatement();
            SyncUtil syncUtil = new SyncUtil();
            String strFormid = syncUtil.getModeIdBytableName("uf_kqycryjlb");
            String ny= dateUtils.previousMonth();
            String strSql=" and ny='"+ny+"'";
            if(isExists(strSql)){
                RecordSet rs_delete=new RecordSet();
                //删除邮件未发送的数据
                rs_delete.execute("delete uf_kqycryjlb where ny='"+ny+"' AND d.yjfszt<>1");
            }
            RecordSet rs=new RecordSet();
            String strQuery="select m.* from v_kq_main m where m.userid not in (SELECT ygid FROM uf_kqycryjlb d WHERE d.ny=m.NF AND d.yjfszt<>1)";
            rs.execute(strQuery);
            String str_xm="";
            String str_userid="";
            String str_gh="";
            String str_year="";
            String str_month="";
            String str_ny="";
            String str_email="";
            while (rs.next()){
                str_xm= Utils.null2String(rs.getString("username"));//人员姓名
                str_userid= Utils.null2String(rs.getString("userid"));//人员id
                str_gh= Utils.null2String(rs.getString("gh"));//工号
                str_year = Utils.null2String(rs.getString("iyear"));//年份
                str_month = Utils.null2String(rs.getString("imonth"));//月份
                str_ny = Utils.null2String(rs.getString("nf"));//年月
                str_email = Utils.null2String(rs.getString("email"));//邮箱
                if(str_gh.equals("")){
                    log.info("人员id='"+str_userid+"'的工号为空");
                }else {
                    String insertSql = "insert into uf_kqycryjlb (ygid,ygxm,year,month,ny,gh,email,yjfszt,formmodeid,modedatacreater) values " +
                            "('" + str_userid + "','" + str_xm + "','" + str_year + "','" + str_month + "','" + str_ny + "','" + str_gh + "','" + str_email + "',0,'" + strFormid + "','1') ";
                    statement.addBatch(insertSql);
                }
            }
            statement.executeBatch();
            conn.close();
            statement.close();
            log.info("批量插入数据成功！");
        }catch (Exception ex){
            log.info("程序异常，异常信息："+ex.toString());
        }
        log.info("计划任务执行结束======>");
    }

    public Boolean isExists(String strSql){
        Boolean result=false;
        RecordSet rs=new RecordSet();
        String strQuery="select * from uf_kqycryjlb where 1=1 "+strSql;
        rs.execute(strQuery);
        while (rs.next()){
            result=true;
        }
        return result;
    }
}
